﻿using FS.Common.DotNetCode;
using FS.Common.DotNetData;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Linq;

namespace FS.Business
{
    public class visa_dao
    {
        #region 下拉选择框
        //系统参数列表
        #region 系统参数
        public DataTable GetSelect_SysParm(string PropertyType)
        {
            DataTable dt = new DataTable();

            if (CacheHelper.GetCache("KeySysParm") == null)
            {
                dt = DataFactory.Run().GetDataTableProc("usp_Cache_GetSelectSysParm", null);
                CacheHelper.Insert("KeySysParm", dt);
            }
            else
            {
                dt = (DataTable)CacheHelper.GetCache("KeySysParm");
            }
            return DataTableHelper.GetNewDataTable(dt, "Property_Type= '" + PropertyType + "'");
        }
        public DataTable GetSelect_SysParm_old(string PropertyType)
        {
            Hashtable ht = new Hashtable();
            ht.Add("property_type", PropertyType);
            
            return DataFactory.Run().GetDataTableProc("usp_GetSelect_SysParm", ht);            
        }

        //系统参数下拉列表
        public DataTable GetSelect_SysParmList()
        {
            return GetSelect_SysParm("syslist");
        }

        //客户类型
        public DataTable GetSelect_trusttype()
        {
            return GetSelect_SysParm("trusttype");
        }

        //价格分类
        public DataTable GetSelect_pricetype()
        {
            return GetSelect_SysParm("pricetype");
        }

        //结算方式
        public DataTable GetSelect_billingOption()
        {
            return GetSelect_SysParm("billingOption");
        }

        //所属区域
        public DataTable GetSelect_CityArea()
        {
            return GetSelect_SysParm("CityArea");
        }

        //寄件类型
        public DataTable GetSelect_postby()
        {
            return GetSelect_SysParm("PostBy");
        }

        //收款方式
        public DataTable GetSelect_moneytype()
        {
            return GetSelect_SysParm("moneytype");
        }

        //收款情况
        public DataTable GetSelect_moneyflag()
        {
            return GetSelect_SysParm("moneyflag");
        }
        
        //系统下拉菜单
        public DataTable GetSelect_Sysparm()
        {
            return GetSelect_SysParm("syslist");
        }

        //签证类型
        public DataTable GetSelect_CertType()
        {
            return GetSelect_SysParm("CertType");
        }

        //签证状态
        public DataTable GetSelect_InfoFlag()
        {
            return GetSelect_SysParm("InfoFlag");
        }

        //签证状态
        public DataTable GetSelect_InfoFlagBeforeStep(int size)
        {
            return DataTableHelper.GetPagedTable(GetSelect_InfoFlag(), 1, size);
        }
        //结算方式
        public DataTable GetSelect_Paymenttype()
        {
            return GetSelect_SysParm("Paymenttype");
        }

        //资料齐全
        public DataTable GetSelect_Dataintegrity()
        {
            return GetSelect_SysParm("Data_integrity");
        }
        
        //销签状态
        public DataTable GetSelect_design()
        {
            return GetSelect_SysParm("design");
        }
        #endregion

        //国家信息
        public DataTable GetSelect_CityInfo()
        {
            //modify 2015.05.04 使用cachae 
            //return DataFactory.Run().GetDataTableProc("usp_GetSelect_citylist", null); 

            DataTable dt = new DataTable();
            if (CacheHelper.GetCache("KeyCity") == null)
            {
                dt = DataFactory.Run().GetDataTableProc("usp_Cache_GetSelectCity", null);
                CacheHelper.Insert("KeyCity", dt);
            }
            else
            {
                dt = (DataTable)CacheHelper.GetCache("KeyCity");
            }
            return dt;
        }

        //根据区域筛选出国家信息
        public DataTable GetSelect_CityInfoByCityArea(string cityarea)
        {            
            //modify 2015.05.04 使用cachae 
            //Hashtable ht = new Hashtable();
            //ht.Add("area", cityarea);
            //return DataFactory.Run().GetDataTableProc("usp_GetSelect_CityListByArea", ht);
            DataTable dt = GetSelect_CityInfo();
            return DataTableHelper.GetNewDataTable(dt, "ref_cityarea= '" + cityarea + "'");           
        }
               
        //获取签证国家下的签证类型
        public DataTable GetSelect_CertTypeInfoByCityID(string cityid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT c.ref_certtype as id,p.property_value as name");
            strSql.Append(" FROM Base_CertCity c inner join sys_parm p ");
            strSql.Append("   on c.ref_certtype=p.property_id ");
            strSql.Append(" where city_id=@cityid");            
            SqlParam[] para = new SqlParam[]
			{
				new SqlParam("@cityid", cityid)
			};
            return DataFactory.Run().GetDataTableBySQL(strSql, para);
        }

        //供应商信息
        public DataTable GetSelect_SupplyInfo()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT Supply_id,Supply_code+'-'+Supply_Name as Supply_Name ");
            strSql.Append(" FROM Base_supply  where deletemark=1");
            return DataFactory.Run().GetDataTableBySQL(strSql);
        }

        //供应商信息
        public DataTable GetSelect_SupplyInfoSimple()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT Supply_id,Supply_Name ");
            strSql.Append(" FROM Base_supply  where deletemark=1");
            return DataFactory.Run().GetDataTableBySQL(strSql);
        }

        //委托方信息
        public DataTable GetSelect_TrustInfo_old()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT trust_id,trust_code+'-'+trust_name as trust_name ");
            strSql.Append(" FROM Base_Trust  where deletemark=1");
            strSql.Append(" order by trust_code");
            return DataFactory.Run().GetDataTableBySQL(strSql);
        }

        public DataTable GetSelect_TrustInfo()
        {
            DataTable dt = new DataTable();

            if (CacheHelper.GetCache("KeyTrustInfo") == null)
            {
                dt = DataFactory.Run().GetDataTableProc("usp_Cache_GetSelectTrust", null);
                CacheHelper.Insert("KeyTrustInfo", dt);
            }
            else
            {
                dt = (DataTable)CacheHelper.GetCache("KeyTrustInfo");
            }
            return dt;           
        }

        #endregion

        #region Form
        #region 数据保存检查
        //检查国家类型维护是否可以修改
        public string Check_CertCity(string City_id, string Ref_CertType, string certcityid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("exec usp_CheckCertCity @city=@city,@certtype=@certtype,@certcityid=@certcityid ");
                      
            SqlParam[] para = new SqlParam[]
			{
				new SqlParam("@city", City_id),
                new SqlParam("@certtype", Ref_CertType),
                new SqlParam("@certcityid", certcityid)
			};

            return Convert.ToString(DataFactory.Run().GetObjectValue(strSql, para));        
        }

        //检查供应商维护是否可以修改
        public string Check_supply(string supply_id, string supplycode)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("exec usp_CheckSupply @supplyid=@supplyid,@supplycode=@supplycode ");

            SqlParam[] para = new SqlParam[]
			{
				new SqlParam("@supplyid", supply_id),
                new SqlParam("@supplycode", supplycode)
			};

            return Convert.ToString(DataFactory.Run().GetObjectValue(strSql, para));
        }

        //检查国家维护是否可以修改
        public string Check_City(string cityid, string citycode)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("exec usp_CheckCity @cityid=@cityid,@citycode=@citycode ");

            SqlParam[] para = new SqlParam[]
			{
				new SqlParam("@cityid", cityid),
                new SqlParam("@citycode", citycode)
			};

            return Convert.ToString(DataFactory.Run().GetObjectValue(strSql, para));
        }

        //检查签证类型维护是否可以修改
        public string Check_CertType(string parmid, string parmname)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("exec usp_CheckCertType @parmid=@parmid,@ParmName=@ParmName ");

            SqlParam[] para = new SqlParam[]
			{
				new SqlParam("@parmid", parmid),
                new SqlParam("@ParmName", parmname)
			};

            return Convert.ToString(DataFactory.Run().GetObjectValue(strSql, para));
        }

        //检查委托方维护是否可以修改
        public string Check_Trust(string trust_id, string trust_code)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("exec usp_CheckTrust @trustid=@trustid,@trustcode=@trustcode ");

            SqlParam[] para = new SqlParam[]
			{
				new SqlParam("@trustid", trust_id),
                new SqlParam("@trustcode", trust_code)
			};

            return Convert.ToString(DataFactory.Run().GetObjectValue(strSql, para));
        }

        //检查委托方维护是否可以修改
        public string Check_SupplyCity(string Supply_id, string City_id, string Ref_CertType, string SupplyCity_ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("exec usp_CheckSupplyCity @Supplyid=@Supplyid,@cityid=@cityid, @certtype=@certtype,@SupplyCity_ID=@SupplyCity_ID");

            SqlParam[] para = new SqlParam[]
			{
				new SqlParam("@Supplyid", Supply_id),
                new SqlParam("@cityid", City_id),
                new SqlParam("@certtype", Ref_CertType),
                new SqlParam("@SupplyCity_ID", SupplyCity_ID)
			};

            return Convert.ToString(DataFactory.Run().GetObjectValue(strSql, para));
        }
                
        #endregion   
        
        //重新计算签证费用
        public bool UpdateInfoPrice(string detailid, string Createuserid, string Createusername)
        {
            Hashtable ht = new Hashtable();
            ht.Add("detailid", detailid);
            ht.Add("Createuserid", Createuserid);
            ht.Add("Createusername", Createusername);

            return (DataFactory.Run().ExecuteByProc("usp_UpdateInfoDetailPrice", ht)>0);  
        }

        

        public Hashtable GetVisaSendtoForm(string infoid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select info.info_no,infoflag.parm_Value as infoflag,");
            strSql.Append("  cus.trust_name,trusttype.parm_Value as trusttype,info.info_operation, ");
            strSql.Append("  info.info_contactuser,info.info_contactnumber,info.info_TuanNo ");
            strSql.Append(" from Visa_Info info ");
            strSql.Append("  inner join Base_Trust cus on info.trust_id=cus.trust_id ");
            strSql.Append("  inner join utf_GetSysParm('infoflag') as infoflag on info.Ref_infoflag=infoflag.parm_Key ");
            strSql.Append("  inner join utf_GetSysParm('trusttype') as trusttype on cus.Ref_trusttype=trusttype.parm_ID ");
            strSql.Append(" where info.Info_ID=@infoid ");

            SqlParam[] parm = new SqlParam[]
			{
				new SqlParam("@infoid", infoid)
			};
            return DataTableHelper.DataTableToHashtable(DataFactory.Run().GetDataTableBySQL(strSql, parm));            
        }

        //单独修改用户供应商
        public bool UpdateDetailSupplyAndCustomer(string pkVal, string ls_supply,string ls_customer,string ls_price,
               string ls_userid, string ls_username)
        {
            bool result;
            try
            {
                StringBuilder[] sqls = new StringBuilder[2];
                object[] objs = new object[2];

                StringBuilder sbadd = new StringBuilder();
                sbadd.Append(" Update Visa_InfoDetail set supply_id=@supply_id, ");
                sbadd.Append("  InfoDetai_EndPrice=@price,infoDetail_user=@customer,");
                sbadd.Append("  modifyuserid=@userid,modifyusername=@username,modifyDate=getdate()");
                sbadd.Append("  where InfoDetail_ID=@detailid");

                SqlParam[] parmAdd = new SqlParam[]
				{
					new SqlParam("@price", ls_price),
                    new SqlParam("@customer", ls_customer),
                    new SqlParam("@supply_id", ls_supply),
					new SqlParam("@userid", ls_userid),
					new SqlParam("@username", ls_username),
					new SqlParam("@detailid", pkVal)
				};
                sqls[0] = sbadd;
                objs[0] = parmAdd;

                StringBuilder sblog = new StringBuilder();
                sblog.Append("exec usp_InsertUserLog @table=@table,@pkid=@pkid,");
                sblog.Append(" @type=@type,@userid=@userid,@username=@username,@rmk=@rmk");

                SqlParam[] parmlog = new SqlParam[]
						{
							new SqlParam("@pkid", pkVal),
							new SqlParam("@userid", ls_userid),
                            new SqlParam("@table", "Visa_InfoDetail"),
                            new SqlParam("@type", "update"),
							new SqlParam("@username", ls_username),
							new SqlParam("@rmk", string.Format("用户{0}修改用户供应商：{1},客人：{3},成本：{2}",ls_username,ls_supply,ls_price,ls_customer))
						};
                sqls[1] = sblog;
                objs[1] = parmlog;

                result = (DataFactory.Run().BatchExecuteBySql(sqls, objs) >= 0);
            }
            catch
            {
                result = false;
            }
            return result;
        }

        #endregion

        #region ajax
        //获取区域下属国家
        public DataTable GetSelect_CityAreaByCityId(string cityid)
        {
            Hashtable ht = new Hashtable();
            ht.Add("cityid", cityid);
            return DataFactory.Run().GetDataTableProc("usp_GetSelect_cityarea", ht);           
        }
        
        //获取供应商报价信息的供应商
        public DataTable GetSelect_SupplyInfoByCityIDAndCertType(string cityid, string certtype)
        {
            Hashtable ht = new Hashtable();
            ht.Add("cityid", cityid);
            ht.Add("certtype", certtype);
            return DataFactory.Run().GetDataTableProc("usp_GetSupplyInfoByCityIDAndCertType", ht);
        }

        //获取委托方信息
        public DataTable GetSelect_trustInfoBytrustid(string trustid)
        {
            Hashtable ht = new Hashtable();
            ht.Add("trustid", trustid);
            return DataFactory.Run().GetDataTableProc("usp_GetTrustInfoByTrustId", ht);
        } 

        //获取国家签证报价
        public string Ajax_GetPriceFromCityCert(string cityid, string certtype, string trusttype)
        {
            
            StringBuilder strSql = new StringBuilder();
            strSql.Append("exec usp_GetPrice_citycert @cityid,@certtype, @trusttype");

            SqlParam[] para = new SqlParam[]
			{				
                new SqlParam("@cityid", cityid),
                new SqlParam("@certtype", certtype),
                new SqlParam("@trusttype", trusttype)
            };
            return Convert.ToString(DataFactory.Run().GetObjectValue(strSql, para));         
        }

        //获取供应商报价
        public string Ajax_GetPriceFromSupply(string cityid, string certtype, string supplyid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select isnull(SupplyCity_EndPrice1,0) from Base_SupplyCity ");
            strSql.Append(" where DeleteMark=1 and Supply_id=@supplyid and City_id=@cityid and Ref_CertType=@certtype");            
            SqlParam[] para = new SqlParam[]
			{
				new SqlParam("@cityid", cityid),
                new SqlParam("@certtype", certtype),
                new SqlParam("@supplyid", supplyid),
            };
            return Convert.ToString(DataFactory.Run().GetObjectValue(strSql, para));  
        }  
        
        //消签管理
        public int Ajax_ChangeVisa(string detailid, string flag,string ls_createuserid,string ls_createusername) 
        {
            Hashtable ht = new Hashtable();
            ht.Add("detailid", detailid);
            ht.Add("flag",flag);
            ht.Add("CreateUserid", ls_createuserid);
            ht.Add("CreateUserName", ls_createusername);           

            return DataFactory.Run().ExecuteByProc("usp_UpdateCancelVisa", ht);
        }

        //获取委托方信息
        public DataTable Ajax_GetDetailInfo(string detailid)
        {
            Hashtable ht = new Hashtable();
            ht.Add("detailid", detailid);
            return DataFactory.Run().GetDataTableProc("usp_GetInfoDetail_ByDetailid", ht);
        }

        public bool Ajax_InsertInfoDetail(string infoid, string ls_name, string ls_cardno, string ls_city, string ls_type,
                 string ls_price, string ls_supplyid, string ls_sprice, string ls_userid, string ls_username, string ls_detailid)
        {
            
            if (ls_sprice==string.Empty) ls_sprice="0";
            if (ls_price == string.Empty) ls_price = "0";

            Hashtable ht = new Hashtable();
            ht.Add("infoid", infoid);
            ht.Add("cityid", ls_city);
            ht.Add("certtype", ls_type);
            ht.Add("user", ls_name);
            ht.Add("cardno", ls_cardno);
            ht.Add("Cprice", ls_price);
            ht.Add("supplyid", ls_supplyid);
            ht.Add("sprice", ls_sprice);
            ht.Add("CreateUserid", ls_userid);
            ht.Add("CreateUserName", ls_username);
            ht.Add("detailid", ls_detailid);
            return (DataFactory.Run().ExecuteByProc("usp_InsertInfoDetailWithSupply", ht) > 0);
        }

        public bool Ajax_InsertInfoDetailNew(string infoid, string ls_cusname, string ls_cuscardno,string ls_cusphone,string ls_city, 
                string ls_type,decimal ldc_price,string ls_supplyid,string ls_Updateuserid, string ls_Updateusername,
                string ls_operationid,string ls_fulldata,string ls_rmk,string detailid)
        {
            Hashtable ht = new Hashtable();
            ht.Add("infoid", infoid);
            ht.Add("cityid", ls_city);
            ht.Add("certtype", ls_type);
            ht.Add("cusname", ls_cusname);
            ht.Add("cuscardno", ls_cuscardno);
            ht.Add("cusrphone", ls_cusphone);
            ht.Add("Cprice", ldc_price);
            ht.Add("supplyid", ls_supplyid);            
            ht.Add("CreateUserid", ls_Updateuserid);
            ht.Add("CreateUserName", ls_Updateusername);
            ht.Add("rmk", ls_rmk);            
            ht.Add("data", ls_fulldata);
            ht.Add("operationid", ls_operationid);
            ht.Add("id", detailid);
            return (DataFactory.Run().ExecuteByProc("usp_InsertInfoDetailNew", ht) > 0);
        }

        public bool Ajax_InsertInfo(string infoid, string ls_updateid, string ls_updatename,
            string trustid, string info_operation, string info_getdate, string ls_AddNo)
        {
            Hashtable ht = new Hashtable();
            ht["trust_id"] = trustid;
            ht["info_operation"] = info_operation;
            ht["info_getdate"] = info_getdate;
            bool IsOk = false;
            if (ls_AddNo == "0")
            {
                ht["Info_ID"] = infoid;
                ht["CreateUserId"] = ls_updateid;
                ht["CreateUserName"] = ls_updatename;
                IsOk = DataFactory.Run().Submit_AddOrEdit("Visa_Info", "Info_ID", null, ht);
            }
            else
            {
                ht["ModifyDate"] = DateTime.Now;
                ht["ModifyUserId"] = ls_updateid;
                ht["ModifyUserName"] = ls_updatename;
                IsOk = DataFactory.Run().Submit_AddOrEdit("Visa_Info", "Info_ID", infoid, ht);
            }
            if (IsOk)
            {
                if (ls_AddNo == "0")
                {
                    //计算订单号
                    ls_AddNo = "1";
                    UpdateInfoNo(infoid);
                }
                return true;
            }
            return false;
        }

        #region 国家用户信息
        //获取所有用户信息
        public DataTable Ajax_GetAllUser()
        {             
            DataTable dt = new DataTable();
            if (CacheHelper.GetCache("AllUser") == null)
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("SELECT User_ID as id ,User_Name as text from Sys_UserInfo ");
                sb.Append(" where DeleteMark=1 and User_Account<>'system'");
                dt = DataFactory.Run().GetDataTableBySQL(sb);
                CacheHelper.Insert("AllUser", dt);
            }
            else
            {
                dt = (DataTable)CacheHelper.GetCache("AllUser");
            }
            return dt;
        }

        //获取城市用户信息
        public DataTable Ajax_GetCityUser(string s_cityid)
        {
            DataTable dt = new DataTable();

            if (CacheHelper.GetCache("CityUser") == null)
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("select cu.id, cu.city_id,u.User_Name as text,u.user_id");
                sb.Append(" from Country_user as cu ");
                sb.Append("  inner join Sys_UserInfo as u on cu.userid=u.User_ID");
                dt = DataFactory.Run().GetDataTableBySQL(sb);
                CacheHelper.Insert("CityUser", dt);
            }
            else
            {
                dt = (DataTable)CacheHelper.GetCache("CityUser");
            }
            return DataTableHelper.GetNewDataTable(dt, "city_id= '" + s_cityid + "'");
        }

        //删除国家用户信息
        public int Ajax_DeleteCountryUser(string[] pkVal)
        {
            int result=0;
            try
            {
                StringBuilder[] sqls = new StringBuilder[pkVal.Length];
                object[] objs = new object[pkVal.Length];

                for (int i = 0; i < pkVal.Length; i++)
                {
                    string item = pkVal[i];
                    if (item.Length > 0)
                    {
                        StringBuilder sb_del = new StringBuilder();
                        sb_del.Append(" delete from Country_user where id=@id");                        
                        SqlParam[] parmDel = new SqlParam[]
						{							
							new SqlParam("@id", item)
						};
                        sqls[i] = sb_del;
                        objs[i] = parmDel;
                    }
                }
                result = DataFactory.Run().BatchExecuteBySql(sqls, objs);
            }
            catch
            {
                result = 0;
            }
            return result;
        }

        //添加国家用户信息
        public int Ajax_AddCountryUser(string cityid, string userid)
        {
            Hashtable ht = new Hashtable();
            ht.Add("cityid", cityid);
            ht.Add("userid", userid);
            return DataFactory.Run().ExecuteByProc("insert_countryuser", ht);
        }
        #endregion
                
        #endregion

        #region 基础信息维护

        #region 国家信息
        //获取国家信息列表
            public DataTable GetBaseCityInfoPage(StringBuilder SqlWhere, IList<SqlParam> IList_param, int pageIndex, int pageSize, ref int count)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT c.City_id,c.City_code,c.City_name,parm.parm_value,");
                strSql.Append(" c.City_remark as rmk2,dbo.utf_GetRemrk(c.City_remark) as rmk, ");
                strSql.Append(" case isCancelVisa when 0 then '否' else '是' end as cancelvisa, ");
                strSql.Append(" c.country_name,c.National_En,c.National_EnName,c.Capital,c.Capital_En ");
                strSql.Append(" from dbo.Base_City c");
                strSql.Append(" inner join dbo.utf_GetSysParm('cityArea') parm on c.Ref_CityArea=parm.parm_id");
                strSql.Append(" where c.DeleteMark=1");
                strSql.Append(SqlWhere);
                //return DataFactory.Run().GetDataTableBySQL(strSql, IList_param.ToArray<SqlParam>());
                return DataFactory.Run().GetPageList(strSql.ToString(), IList_param.ToArray<SqlParam>(), "City_code", "asc", pageIndex, pageSize, ref count);
            }
            #endregion

            #region 国家签证
            //获取国家签证信息列表
            public DataTable GetBaseCertCityInfoPage(StringBuilder SqlWhere, IList<SqlParam> IList_param, int pageIndex, int pageSize, ref int count)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT cc.CertCity_id,c.City_name,parmtype.parm_value as certtype,cc.CertCity_WorkDay,");
                strSql.Append(" cc.CertCity_PicNum,cc.CertCity_EffectDay,cc.CertCity_StayDay,cc.Supply_ID,");
                strSql.Append(" cc.CertCity_Price,cc.CertCity_specialPrice,cc.CertCity_InternalPrice,");
                strSql.Append(" cc.CertCity_remark as rmk2,dbo.utf_GetRemrk(cc.CertCity_remark) as rmk");
                strSql.Append(" from Base_CertCity as cc");
                strSql.Append(" inner join Base_City as c on c.City_id=cc.City_id");
                strSql.Append(" inner join dbo.utf_getsysparm('certtype') as parmtype on parmtype.parm_id=cc.Ref_CertType");
                strSql.Append(" where cc.DeleteMark = 1");
                strSql.Append(SqlWhere);
                return DataFactory.Run().GetPageList(strSql.ToString(), IList_param.ToArray<SqlParam>(), "City_name", "asc", pageIndex, pageSize, ref count);
            }
            #endregion

            #region 签证类型
            //获取签证类型信息列表
            public DataTable GetCertTypeInfoPage(StringBuilder SqlWhere, IList<SqlParam> IList_param, int pageIndex, int pageSize, ref int count)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT Property_ID, Property_Value,Property_remark ");
                strSql.Append(" FROM sys_parm  ");
                strSql.Append("  where DeleteMark=1 and property_type='CertType'");
                strSql.Append(SqlWhere);
                return DataFactory.Run().GetPageList(strSql.ToString(), IList_param.ToArray<SqlParam>(), "Property_Value", "asc", pageIndex, pageSize, ref count);
            }
            #endregion

            #region 系统参数 --包含签证类型
            //获取系统参数信息列表
            public DataTable GetSysParmInfoPage(StringBuilder SqlWhere, IList<SqlParam> IList_param, int pageIndex, int pageSize, ref int count)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT a.Property_ID, a.Property_Type, a.Property_Key, a.Property_Value,a.Property_remark ");
                strSql.Append(" FROM Sys_Parm a ");
                strSql.Append(" inner join Sys_Parm b  on b.Property_Key=a.Property_Type and b.Property_Type='syslist' and b.DeleteMark=1 ");
                strSql.Append(" where a.DeleteMark=1 ");
                strSql.Append(SqlWhere);

                return DataFactory.Run().GetPageList(strSql.ToString(), IList_param.ToArray<SqlParam>(), "Property_Type", "asc", pageIndex, pageSize, ref count);
            }
            #endregion

            #region 委托人信息
            //获取委托人列表
            public DataTable GetBaseTrustInfoPage(StringBuilder SqlWhere, IList<SqlParam> IList_param, int pageIndex, int pageSize, ref int count)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select t.trust_id,t.trust_code,t.trust_name,trusttype.parm_Value as type_trust,trust_user,");
                strSql.Append(" pricetype.parm_Value as type_price,t.trust_contactuser,t.trust_contactnumber,bill.parm_Value as bill,");
                strSql.Append(" t.trust_remark as rmk2,dbo.utf_GetRemrk(t.trust_remark) as rmk ");
                strSql.Append(" from Base_Trust t");
                strSql.Append("     inner join utf_GetSysParm('trusttype') as trusttype on t.Ref_trusttype=trusttype.parm_ID ");
                strSql.Append("     inner join utf_GetSysParm('pricetype') as pricetype on t.Ref_pricetype=pricetype.parm_ID");
                strSql.Append("     left join utf_GetSysParm('billingOption') as bill on t.Ref_billingOption=bill.parm_ID ");
                strSql.Append(" where t.DeleteMark=1");
                strSql.Append(SqlWhere);
                return DataFactory.Run().GetPageList(strSql.ToString(), IList_param.ToArray<SqlParam>(), "trust_code", "asc", pageIndex, pageSize, ref count);
            }
            #endregion

            #region 供应商信息
            //获取供应商列表
            public DataTable GetBaseSupplyInfoPage(StringBuilder SqlWhere, IList<SqlParam> IList_param, int pageIndex, int pageSize, ref int count)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select s.Supply_id,s.Supply_Code,s.Supply_Name,parm_Value,pay.parm_Value as payment, ");
                strSql.Append(" s.Supply_contactuser,s.Supply_contactnumber,");
                strSql.Append(" s.Supply_remark as rmk2,dbo.utf_GetRemrk(s.Supply_remark) as rmk ");
                strSql.Append(" from Base_supply s ");
                strSql.Append(" inner join utf_GetSysParm('Paymenttype') as pay on s.Ref_Paymenttype=pay.parm_ID ");
                strSql.Append(" where s.DeleteMark=1 ");
                strSql.Append(SqlWhere);
                return DataFactory.Run().GetPageList(strSql.ToString(), IList_param.ToArray<SqlParam>(), "Supply_Code", "asc", pageIndex, pageSize, ref count);
            }
            #endregion

            #region 供应商报价
            //获取供应商报价信息列表
            public DataTable GetBaseSupplyCityInfoPage(StringBuilder SqlWhere, IList<SqlParam> IList_param, int pageIndex, int pageSize, ref int count)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT sc.SupplyCity_ID, s.Supply_Name, c.City_name, area.parm_Value as type_area, certtype.parm_Value as type_cert, ");
                strSql.Append(" sc.SupplyCity_WorkDay, sc.SupplyCity_EndPrice1, sc.SupplyCity_EndPrice2,sc.SupplyCity_EndPrice3,");
                strSql.Append(" case sc.SupplyCity_IsDefault when 1 then '是' else '否' end as isdefault,");
                strSql.Append(" sc.SupplyCity_remark as rmk2,dbo.utf_GetRemrk(sc.SupplyCity_remark) as rmk  ");
                strSql.Append(" FROM Base_SupplyCity sc");
                strSql.Append(" inner join Base_supply s on sc.Supply_id=s.Supply_id");
                strSql.Append(" inner join Base_City c on sc.City_id=c.City_id ");
                strSql.Append(" left join utf_GetSysParm('cityarea') as area on sc.Ref_CityArea=area.parm_ID");
                strSql.Append(" inner join utf_GetSysParm('CertType') as certtype on sc.Ref_CertType=certtype.parm_ID ");
                strSql.Append(" where sc.DeleteMark = 1");
                strSql.Append(SqlWhere);
                return DataFactory.Run().GetPageList(strSql.ToString(), IList_param.ToArray<SqlParam>(), "City_name", "asc", pageIndex, pageSize, ref count);
            }  
            #endregion        
            
        #endregion               

        #region 消签管理
        //获取消签信息列表
        public DataTable GetCancelVisaInfoPage(StringBuilder SqlWhere, IList<SqlParam> IList_param, int pageIndex, int pageSize, ref int count)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select trust.trust_name,info.info_operation,detail.InfoDetail_user,detail.InfoDetail_cardno,detail.infodetail_userphone,  ");
            strSql.Append(" Convert(varchar(10),detail.InfoDetail_submitdate,120) as submitdate, ");
            strSql.Append(" Convert(varchar(10),detail.InfoDetail_receivedate,120) as receivedate,  ");
            strSql.Append(" city.City_name,certtype.parm_Value as certname,detail.InfoDetail_ID,detail.createdate,");
            strSql.Append(" case detail.infodetail_design when 0 then '未销签' else '已销签' end design, ");
            strSql.Append(" info.info_remark as rmk2,dbo.utf_GetRemrk(info.info_remark) as rmk  ");
            strSql.Append("  from Visa_Info info ");
            strSql.Append("  inner join Visa_InfoDetail as detail on info.Info_ID=detail.Info_ID and info.DeleteMark=1");
            strSql.Append("  inner join Base_City city on city.City_id=detail.City_id ");
            strSql.Append("  inner join Base_Trust trust on trust.trust_id=info.trust_id ");
            strSql.Append("  inner join utf_GetSysParm('certtype') as certtype on certtype.parm_ID=detail.Ref_CertType ");
            strSql.Append("  where info.DeleteMark=1 and city.isCancelVisa=1 and detail.Ref_infoflag='post'");
            strSql.Append(SqlWhere);
            return DataFactory.Run().GetPageList(strSql.ToString(), IList_param.ToArray<SqlParam>(), "createdate", "asc", pageIndex, pageSize, ref count);
        }
        #endregion

        #region 寄件信息
        //获取寄件信息列表
        public DataTable GetPostVisaInfoPage(StringBuilder SqlWhere, IList<SqlParam> IList_param, int pageIndex, int pageSize, ref int count)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select detail.InfoDetail_ID,detail.InfoDetail_user,detail.InfoDetail_cardno,");
            strSql.Append("	city.City_name,certtype.parm_Value as certname,supply.Supply_Name, ");
            strSql.Append(" convert(varchar(10),ISNULL(detail.InfoDetail_Submitdate,info.info_submitdate),120) as submitdate,");
            strSql.Append(" info.info_remark as rmk2,dbo.utf_GetRemrk(info.info_remark) as rmk,detail.createdate ");
            strSql.Append(" from Visa_Info info ");
            strSql.Append("  inner join Visa_InfoDetail detail on info.Info_ID=detail.Info_ID ");
            strSql.Append("  inner join Base_City as city on city.City_id=detail.City_id ");
            strSql.Append("  inner join utf_GetSysParm('certtype') as certtype on certtype.parm_ID=detail.Ref_CertType ");
            strSql.Append("  inner join Base_supply supply on supply.Supply_id=detail.Supply_id ");
            strSql.Append(" where info.DeleteMark=1 and detail.DeleteMark=1 and detail.Data_integrity=1 ");
            strSql.Append("     and info.Ref_infoflag='SendTo' ");
            strSql.Append(SqlWhere);
            return DataFactory.Run().GetPageList(strSql.ToString(), IList_param.ToArray<SqlParam>(), "createdate,", "asc", pageIndex, pageSize, ref count);
        }

        //编辑菜单
        public Hashtable GetPostVisaForm(string detailid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select info.info_no,infoflag.parm_Value as flagname,detail.City_id,detail.Ref_CertType,detail.Supply_id,");
            strSql.Append(" convert(varchar(10),info.info_getdate,120) as info_getdate,");
            //strSql.Append(" convert(varchar(10),info.info_submitdate,120) as info_submitdate,");
            strSql.Append(" convert(varchar(10),isnull(detail.InfoDetail_Submitdate,info.info_submitdate),120) as Submitdate,");
            strSql.Append(" detail.InfoDetail_user,detail.InfoDetail_cardno,detail.InfoDetail_remark ");
            strSql.Append(" from Visa_Info info ");
            strSql.Append("  inner join Visa_InfoDetail detail on info.Info_ID=detail.Info_ID");
            strSql.Append(" inner join utf_GetSysParm('infoflag') infoflag on infoflag.parm_Key=info.Ref_infoflag");
            strSql.Append(" where info.DeleteMark=1 and detail.DeleteMark=1 and detail.InfoDetail_ID=@detailid");

            SqlParam[] parm = new SqlParam[]
			{
				new SqlParam("@detailid", detailid)
			};
            return DataTableHelper.DataTableToHashtable(DataFactory.Run().GetDataTableBySQL(strSql, parm));
        }
        #endregion

        #region 出签信息
        //获取出签信息列表
        public DataTable GetResultVisaInfoPage(StringBuilder SqlWhere, IList<SqlParam> IList_param, int pageIndex, int pageSize, ref int count)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append(" select info.Info_ID,info.info_no,trust.trust_name,info.info_contactuser,info.info_contactnumber,");
            strSql.Append("  info.info_outpeople,Convert(char(10),info.info_getdate,120) as getdate,");
            strSql.Append("  Convert(char(10),info.info_submitdate,120) as submitdate,detail.createdate, ");
            strSql.Append("  info.info_remark as rmk2,dbo.utf_GetRemrk(info.info_remark) as rmk ");
            strSql.Append(" from Visa_Info info");
            strSql.Append("  inner join Base_Trust trust on trust.trust_id=info.trust_id");
            strSql.Append(" where  info.DeleteMark=1 and info.Ref_infoflag='sendto'");

            strSql.Append(SqlWhere);
            return DataFactory.Run().GetPageList(strSql.ToString(), IList_param.ToArray<SqlParam>(), "createdate,", "asc", pageIndex, pageSize, ref count);
        }

        //获取出签信息详情
        public DataTable GetResultVisaInfoDetail(StringBuilder SqlWhere, IList<SqlParam> IList_param, int pageIndex, int pageSize, ref int count)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select convert(varchar(10),detail.InfoDetail_Submitdate,120) as submitdate,");
            strSql.Append("	detail.infodetail_user,trust.trust_name,s.Supply_Name, detail.createdate,");
            strSql.Append("	detail.infodetail_id,c.city_name,certtype.parm_Value as certname, ");
            strSql.Append("	infoflag.parm_Value as infoname,detail.InfoDetai_BegPrice, ");
            strSql.Append("	convert(varchar(10),detail.date_sendback,120) as date_sendback, ");            
            strSql.Append("	detail.InfoDetail_operationname as user_oper,detail.user_audit,detail.user_sendto, ");            
            strSql.Append("	detail.InfoDetail_remark as rmk2,dbo.utf_GetRemrk(detail.InfoDetail_remark) as rmk ");
            strSql.Append("from Visa_InfoDetail detail ");
            strSql.Append("	inner join Visa_Info info on info.Info_ID=detail.Info_ID and info.DeleteMark=1 ");
            strSql.Append("	inner join base_city c on detail.city_id=c.city_id ");
            strSql.Append("	inner join Base_Trust trust on trust.trust_id=info.trust_id ");
            strSql.Append("	left join Base_supply s on detail.Supply_id=s.Supply_id ");
            strSql.Append("	inner join utf_GetSysParm('certtype') as certtype on certtype.parm_ID=detail.ref_certtype ");
            strSql.Append("	inner join utf_GetSysParm('infoflag') as infoflag on infoflag.parm_Key=detail.Ref_infoflag 	");
            strSql.Append("where detail.DeleteMark=1 and detail.Ref_infoflag='sendto'");
            strSql.Append(SqlWhere);
            return DataFactory.Run().GetPageList(strSql.ToString(), IList_param.ToArray<SqlParam>(), "date_sendback", "asc", pageIndex, pageSize, ref count);
        }
        public DataTable GetResultVisaInfoExport(StringBuilder SqlWhere, IList<SqlParam> IList_param)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select convert(varchar(10),detail.InfoDetail_Submitdate,120) as '送签时间',");
            strSql.Append("	detail.infodetail_user as '客户信息',trust.trust_name as '委托方',s.Supply_Name as '供应商', ");
            strSql.Append("	c.city_name as '国家',certtype.parm_Value as '签证类型', detail.date_sendback as '截至时间',");
            strSql.Append("	infoflag.parm_Value as '订单状态',detail.InfoDetai_BegPrice as '销售价', ");
            strSql.Append("	detail.InfoDetail_operationname as '操作员',detail.user_audit as '审核员',detail.user_sendto as '送签员', ");
            strSql.Append("	detail.InfoDetail_remark as '备注' ");
            strSql.Append("from Visa_InfoDetail detail ");
            strSql.Append("	inner join Visa_Info info on info.Info_ID=detail.Info_ID and info.DeleteMark=1 ");
            strSql.Append("	inner join base_city c on detail.city_id=c.city_id ");
            strSql.Append("	inner join Base_Trust trust on trust.trust_id=info.trust_id ");
            strSql.Append("	left join Base_supply s on detail.Supply_id=s.Supply_id ");
            strSql.Append("	inner join utf_GetSysParm('certtype') as certtype on certtype.parm_ID=detail.ref_certtype ");
            strSql.Append("	inner join utf_GetSysParm('infoflag') as infoflag on infoflag.parm_Key=detail.Ref_infoflag 	");
            strSql.Append("where detail.DeleteMark=1 and detail.Ref_infoflag='sendto'");
            strSql.Append(SqlWhere);
            return DataFactory.Run().GetDataTableBySQL(strSql, IList_param.ToArray<SqlParam>());
        }

        public string GetVisaDetailForm(string detailid)
        {
            StringBuilder sql = new StringBuilder("exec usp_GetList_VisaDetail @detailid");
            SqlParam[] para = new SqlParam[]
			    {
				    new SqlParam("@detailid", detailid)
                };
            return DataFactory.Run().GetObjectValue(sql,para).ToString();            
        }

        //个别客户送签
        public bool UpdateDetailSendDate(string pkVal, string ls_date, string ls_flag, string ls_userid, string ls_username)
        {
            bool result;
            try
            {
                StringBuilder[] sqls = new StringBuilder[2];
                object[] objs = new object[2];

                StringBuilder sbadd = new StringBuilder();
                sbadd.Append(" Update Visa_InfoDetail set InfoDetail_Submitdate=@date,Data_integrity=@flag, ");
                sbadd.Append("  modifyuserid=@userid,modifyusername=@username,modifyDate=getdate()");
                sbadd.Append("  where InfoDetail_ID=@detailid");

                SqlParam[] parmAdd = new SqlParam[]
				{
					new SqlParam("@date", ls_date),
					new SqlParam("@flag", ls_flag),
                    new SqlParam("@userid", ls_userid),
					new SqlParam("@username", ls_username),
					new SqlParam("@detailid", pkVal)
				};
                sqls[0] = sbadd;
                objs[0] = parmAdd;

                StringBuilder sblog = new StringBuilder();
                sblog.Append("exec usp_InsertUserLog @table=@table,@pkid=@pkid,");
                sblog.Append(" @type=@type,@userid=@userid,@username=@username,@rmk=@rmk");

                SqlParam[] parmlog = new SqlParam[]
						{
							new SqlParam("@pkid", pkVal),
							new SqlParam("@userid", ls_userid),
							new SqlParam("@username", ls_username),
                            new SqlParam("@table", "Visa_InfoDetail"),
							new SqlParam("@type", "update"),
							new SqlParam("@rmk", string.Format("用户{0}更新送签时间{1}",ls_username,ls_date))
						};
                sqls[1] = sblog;
                objs[1] = parmlog;

                result = (DataFactory.Run().BatchExecuteBySql(sqls, objs) >= 0);
            }
            catch
            {
                result = false;
            }
            return result;
        }

        //客户批量送签
        public bool UpdateDetailBatchSendDate(string pkVal, string ls_date, string ls_userid, string ls_username)
        {
            Hashtable ht = new Hashtable();
            ht.Add("infoid", pkVal);
            ht.Add("date", ls_date);
            ht.Add("userid", ls_userid);
            ht.Add("username", ls_username);

            return (DataFactory.Run().ExecuteByProc("usp_UpdateDetailSendDateForAll", ht) >= 0);
        }

        //个别客户出签
        public int UpdateDetailReceiveDate(string pkVal, string ls_date, string updateid, string updatename,string requestname)
        {
            Hashtable ht = new Hashtable();
            ht.Add("key", pkVal);
            ht.Add("receive_date", ls_date);
            ht.Add("updateid", updateid);
            ht.Add("updatename", updatename);
            ht.Add("userResult", requestname);
            return DataFactory.Run().ExecuteByProc("usp_UpdateInfoReceive", ht);             
        }

        //个别客户拒签
        public int UpdateDetailRejectDate(string pkVal, string ls_date, string ls_reason, string updateid, string updatename,string requestname)
        {
            Hashtable ht = new Hashtable();
            ht.Add("key", pkVal);
            ht.Add("reject_date", ls_date);
            ht.Add("reject_reason", ls_reason);
            ht.Add("updateid", updateid);
            ht.Add("updatename", updatename);
            ht.Add("userResult", requestname);
            return DataFactory.Run().ExecuteByProc("usp_UpdateInfoReject", ht);             
        }
        #endregion

        #region 用户寄件管理
        //获取出签信息详情
        public DataTable GetInfoPostList(StringBuilder SqlWhere, IList<SqlParam> IList_param, int pageIndex, int pageSize, ref int count)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select detail.infodetail_id,trust.trust_name,detail.infodetail_user,");
            strSql.Append("	detail.InfoDetail_userphone,c.city_name,certtype.parm_Value as certname, ");
            strSql.Append("	convert(varchar(10),detail.InfoDetail_Submitdate,120) as submitdate, ");
            strSql.Append("	infoflag.parm_Value as infoname,detail.InfoDetai_BegPrice,moneyflag.parm_Value as moneyname, ");
            strSql.Append("	detail.createdate,detail.InfoDetail_operationname as user_oper,detail.user_audit,");
            strSql.Append(" detail.user_sendto,detail.user_result, ");
            strSql.Append("	detail.InfoDetail_remark as rmk2,dbo.utf_GetRemrk(detail.InfoDetail_remark) as rmk ");
            strSql.Append("from Visa_InfoDetail detail ");
            strSql.Append("	inner join Visa_Info info on info.Info_ID=detail.Info_ID and info.DeleteMark=1 ");
            strSql.Append("	inner join base_city c on detail.city_id=c.city_id ");
            strSql.Append("	inner join Base_Trust trust on trust.trust_id=info.trust_id ");
            strSql.Append("	inner join utf_GetSysParm('certtype') as certtype on certtype.parm_ID=detail.ref_certtype ");
            strSql.Append("	inner join utf_GetSysParm('infoflag') as infoflag on infoflag.parm_Key=detail.Ref_infoflag ");            
            strSql.Append("	left join utf_GetSysParm('moneyflag') moneyflag on moneyflag.parm_Key=detail.Ref_moneyflag ");
            strSql.Append("where detail.DeleteMark=1 and ");
            strSql.Append("	(detail.Ref_infoflag='ResultOK' or detail.Ref_infoflag='ResultFalse') ");
            strSql.Append(SqlWhere);
            return DataFactory.Run().GetPageList(strSql.ToString(), IList_param.ToArray<SqlParam>(), "createdate" , "asc", pageIndex, pageSize, ref count);
        }

        public DataTable GetInfoPostExport(StringBuilder SqlWhere, IList<SqlParam> IList_param)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select trust.trust_name as '委托方',detail.infodetail_user as '客户',");
            strSql.Append("	detail.InfoDetail_userphone as '客户电话',c.city_name as '国家',certtype.parm_Value  as '签证类型', ");
            strSql.Append("	convert(varchar(10),detail.InfoDetail_Submitdate,120)  as '送签时间', ");
            strSql.Append("	infoflag.parm_Value  as '订单状态',detail.InfoDetai_BegPrice as '销售额',moneyflag.parm_Value as '收款情况', ");
            strSql.Append("	detail.InfoDetail_operationname  as '操作员',detail.user_audit  as '审核员',");
            strSql.Append(" detail.user_sendto as '送签员',detail.user_result  as '出签员', ");
            strSql.Append("	detail.InfoDetail_remark  as '备注' ");
            strSql.Append("from Visa_InfoDetail detail ");
            strSql.Append("	inner join Visa_Info info on info.Info_ID=detail.Info_ID and info.DeleteMark=1 ");
            strSql.Append("	inner join base_city c on detail.city_id=c.city_id ");
            strSql.Append("	inner join Base_Trust trust on trust.trust_id=info.trust_id ");
            strSql.Append("	inner join utf_GetSysParm('certtype') as certtype on certtype.parm_ID=detail.ref_certtype ");
            strSql.Append("	inner join utf_GetSysParm('infoflag') as infoflag on infoflag.parm_Key=detail.Ref_infoflag ");
            strSql.Append("	left join utf_GetSysParm('moneyflag') moneyflag on moneyflag.parm_Key=detail.Ref_moneyflag ");
            strSql.Append("where detail.DeleteMark=1 and ");
            strSql.Append("	(detail.Ref_infoflag='ResultOK' or detail.Ref_infoflag='ResultFalse') ");
            strSql.Append(SqlWhere);
            return DataFactory.Run().GetDataTableBySQL(strSql, IList_param.ToArray<SqlParam>());
        }

        //用户寄件
        public int UpdateInfoPostForm(string pkVal, string ls_posyby, string ls_postmoney, string ls_postno,
            string ls_postrmk,string updateid, string updatename, string requestname)
        {
            Hashtable ht = new Hashtable();
            ht.Add("key", pkVal);
            ht.Add("postby", ls_posyby);
            ht.Add("postmoney", ls_postmoney);
            ht.Add("postno", ls_postno);
            ht.Add("postrmk", ls_postrmk);            
            ht.Add("updateid", updateid);
            ht.Add("updatename", updatename);
            ht.Add("userResult", requestname);
            return DataFactory.Run().ExecuteByProc("usp_UpdateInfoPost", ht);           
        } 
        #endregion

        #region 财务应收款管理
        public DataTable GetInfoFinanceList(StringBuilder SqlWhere, IList<SqlParam> IList_param, int pageIndex, int pageSize, ref int count)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select detail.infodetail_id,trust.trust_name,detail.infodetail_user,");
            strSql.Append("	detail.InfoDetail_userphone,c.city_name,certtype.parm_Value as certname, ");
            strSql.Append("	convert(varchar(10),detail.InfoDetail_Receivedate,120) as Receivedate, ");
            strSql.Append(" detail.InfoDetai_BegPrice,info.Info_fapiao,info.info_moneyfapiao,detail.createdate,");
            strSql.Append("	infoflag.parm_Value as infoname,moneyflag.parm_Value as moneyname, moneytype.parm_Value as moneytype, ");
            strSql.Append("	Postby.parm_Value as postname,post.Postmoney,post.PostNo,detail.InfoDetail_Account,  ");
            strSql.Append("	post.Post_Remark as rmk2,dbo.utf_GetRemrk(post.Post_Remark) as rmk ");
            strSql.Append("from Visa_InfoDetail detail ");
            strSql.Append("	inner join Visa_Info info on info.Info_ID=detail.Info_ID and info.DeleteMark=1 ");
            strSql.Append("	inner join base_city c on detail.city_id=c.city_id ");
            strSql.Append("	inner join Base_Trust trust on trust.trust_id=info.trust_id ");
            strSql.Append("	inner join utf_GetSysParm('certtype') as certtype on certtype.parm_ID=detail.ref_certtype ");
            strSql.Append("	inner join utf_GetSysParm('infoflag') as infoflag on infoflag.parm_Key=detail.Ref_infoflag ");
            strSql.Append("	left join Visa_Post post on post.infodetail_id=detail.InfoDetail_ID ");
            strSql.Append("	left join utf_GetSysParm('Postby') as Postby on Postby.parm_Key=post.ref_Postby ");
            strSql.Append("	left join utf_GetSysParm('moneyflag') moneyflag on moneyflag.parm_Key=detail.Ref_moneyflag ");
            strSql.Append("	left join utf_GetSysParm('moneytype') moneytype on moneytype.parm_Key=detail.Ref_moneytype ");
            strSql.Append("where detail.DeleteMark=1  and detail.Ref_infoflag='post'  ");            
            strSql.Append(SqlWhere);
            return DataFactory.Run().GetPageList(strSql.ToString(), IList_param.ToArray<SqlParam>(), "createdate,", "asc", pageIndex, pageSize, ref count);
        }

        //财务信息修改
        public bool UpdateInfoFinanceForm(string[] pkVal, string ls_moneyflag,string ls_moneytype, string ls_account,string ls_userid, string ls_username)
        {
            bool result;
            try
            {
                StringBuilder[] sqls = new StringBuilder[pkVal.Length * 2];
                object[] objs = new object[pkVal.Length * 2];

                for (int i = 0; i < pkVal.Length; i++)
                {
                    string item = pkVal[i];
                    if (item.Length > 0)
                    {
                        StringBuilder sbadd = new StringBuilder();
                        sbadd.Append(" Update Visa_InfoDetail set Ref_moneyflag=@Ref_moneyflag,Ref_moneytype=@Ref_moneytype,InfoDetail_Account=@InfoDetail_Account,");
                        sbadd.Append("  modifyuserid=@userid,modifyusername=@username,modifyDate=getdate()");
                        sbadd.Append("  where InfoDetail_ID=@detailid");

                        SqlParam[] parmAdd = new SqlParam[]
						{
							new SqlParam("@userid", ls_userid),
							new SqlParam("@username", ls_username),
							new SqlParam("@detailid", item),
                            new SqlParam("@Ref_moneyflag", ls_moneyflag),
                            new SqlParam("@Ref_moneytype", ls_moneytype),
                            new SqlParam("@InfoDetail_Account", ls_account)
						};
                        sqls[i * 2] = sbadd;
                        objs[i * 2] = parmAdd;

                        StringBuilder sblog = new StringBuilder();
                        sblog.Append("exec usp_InsertUserLog @table=@table,@pkid=@pkid,");
                        sblog.Append(" @type=@type,@userid=@userid,@username=@username,@rmk=@rmk");
                        SqlParam[] parmlog = new SqlParam[]
						{
							new SqlParam("@pkid", item),
							new SqlParam("@userid", ls_userid),
							new SqlParam("@username", ls_username),                            
                            new SqlParam("@table", "Visa_InfoDetail"),
                            new SqlParam("@type", "update"),
							new SqlParam("@rmk", string.Format("用户{0}更新财务信息,付款情况{1},付款方式{2},转账帐户{3}",ls_username,ls_moneyflag, ls_moneytype, ls_account))
						};
                        sqls[i * 2 + 1] = sblog;
                        objs[i * 2 + 1] = parmlog;
                    }
                }
                result = (DataFactory.Run().BatchExecuteBySql(sqls, objs) >= 0);
            }
            catch
            {
                result = false;
            }
            return result;
        } 
        #endregion


        #region 签证信息维护
        //获取签证录入信息列表
            public DataTable GetVisaInfoPage(StringBuilder SqlWhere, IList<SqlParam> IList_param, int pageIndex, int pageSize, ref int count)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT v.Info_ID, v.info_no, t.trust_name, v.info_operation,v.info_contactnumber,  ");
                strSql.Append(" convert(varchar(10),v.info_getdate,120) as 'getdate',");
                strSql.Append(" convert(varchar(10),d.InfoDetail_Submitdate,120) as 'Submitdate',");
                strSql.Append(" convert(varchar(10),d.InfoDetail_Receivedate,120) as 'Receivedate',");
                strSql.Append(" v.info_TuanNo,v.info_outpeople,moneyflag.parm_Value as moneyname,");
                strSql.Append(" v.info_remark as rmk2,dbo.utf_GetRemrk(v.info_remark) as rmk  ");
                strSql.Append(" FROM Visa_Info v ");
                strSql.Append("  inner join Visa_InfoDetail d on v.Info_ID=d.Info_ID and d.DeleteMark=1 ");
                strSql.Append("  inner join dbo.Base_Trust t on v.trust_id=t.trust_id ");
                strSql.Append("  left join dbo.utf_GetSysParm('moneyflag') as moneyflag on moneyflag.parm_Key=v.Ref_moneyflag ");
                strSql.Append("  where v.DeleteMark=1 ");
                strSql.Append(SqlWhere);
                return DataFactory.Run().GetPageList(strSql.ToString(), IList_param.ToArray<SqlParam>(), "info_no", "asc", pageIndex, pageSize, ref count);
            }

            //public DataTable GetVisaInfoDetailPage(StringBuilder SqlWhere, IList<SqlParam> IList_param, int pageIndex, int pageSize, ref int count)
            //{
            //  StringBuilder strSql = new StringBuilder();              
            //  strSql.Append("SELECT d.InfoDetail_ID, v.info_no, t.trust_name, ");
            //  strSql.Append(" convert(varchar(10),v.info_getdate,120) as 'info_getdate',");
            //  strSql.Append(" convert(varchar(10),d.InfoDetail_Submitdate,120) as 'Submitdate',");
            //  strSql.Append(" convert(varchar(10),d.InfoDetail_Receivedate,120) as 'Receivedate', ");
            //  strSql.Append(" convert(varchar(10),d.Data_CompletionDate,120) as 'CompletionDate',d.createdate, ");
            //  strSql.Append(" d.InfoDetail_user,d.InfoDetail_userphone,d.InfoDetail_cardno,infoflag.parm_Value as infoname, ");
            //  strSql.Append(" fulldata.parm_Value as dataname,moneyflag.parm_Value as moneyname,d.InfoDetail_operationname, ");
            //  strSql.Append(" d.InfoDetail_remark as rmk2,dbo.utf_GetRemrk(d.InfoDetail_remark) as rmk  ");
            //  strSql.Append("FROM Visa_Info v ");
            //  strSql.Append("	inner join Visa_InfoDetail d on v.Info_ID=d.Info_ID and d.DeleteMark=1 ");
            //  strSql.Append("	inner join dbo.Base_Trust t on v.trust_id=t.trust_id ");
            //  strSql.Append("	inner join utf_GetSysParm('infoflag') infoflag on infoflag.parm_Key=d.Ref_infoflag ");
            //  strSql.Append("	inner join utf_GetSysParm('Data_integrity') fulldata on fulldata.parm_Key=d.Data_integrity ");
            //  strSql.Append("	left join dbo.utf_GetSysParm('moneyflag') as moneyflag on moneyflag.parm_Key=v.Ref_moneyflag ");
            //  strSql.Append("where v.DeleteMark=1 ");

            //  strSql.Append(SqlWhere);
            //  return DataFactory.Run().GetPageList(strSql.ToString(), IList_param.ToArray<SqlParam>(), "createdate", "asc", pageIndex, pageSize, ref count);
            //}

            #region Form数据检查
            public string Check_InfoDetail(string infoid, string ls_name, string ls_city, string ls_type, string ls_detailid)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("exec usp_CheckInfoDetail @info_id,@city_id, @certtype,@user,@detailid");

                SqlParam[] para = new SqlParam[]
			    {
				    new SqlParam("@info_id", infoid),
                    new SqlParam("@city_id", ls_city),
                    new SqlParam("@certtype", ls_type),
                    new SqlParam("@user", ls_name),
                    new SqlParam("@detailid", ls_detailid)
                };
                return Convert.ToString(DataFactory.Run().GetObjectValue(strSql, para));
            }

            public bool InsertInfoDetail(string infoid, string ls_name, string ls_cardno, string ls_city, string ls_type,
                    string ls_price, string ls_userid, string ls_username,string ls_detailid)
            {            
                Hashtable ht = new Hashtable();
                ht.Add("infoid", infoid);
                ht.Add("cityid", ls_city);
                ht.Add("certtype", ls_type);
                ht.Add("user", ls_name);
                ht.Add("cardno", ls_cardno);
                ht.Add("Cprice", ls_price);
                ht.Add("CreateUserid", ls_userid);
                ht.Add("CreateUserName", ls_username);
                ht.Add("detailid", ls_detailid);
                return (DataFactory.Run().ExecuteByProc("usp_InsertInfoDetail", ht) > 0);           
            }

            public DataTable GetInfoDetail(string infoid)
            {
                Hashtable ht = new Hashtable();
                ht.Add("info_id", infoid);
                return DataFactory.Run().GetDataTableProc("usp_GetInfoDetail", ht);
            }

            //获取签证汇总数据
            public DataTable GetTotalInfoDetail(string infoid)
            {
                Hashtable ht = new Hashtable();
                ht.Add("info_id", infoid);
                return DataFactory.Run().GetDataTableProc("usp_GetInfoDetailTotal", ht);
            }

            public int DeleteInfoDetail(string infodetailid)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("Update Visa_InfoDetail set deletemark=0 where InfoDetail_ID = @id");
                SqlParam[] para = new SqlParam[]
			    {
				    new SqlParam("@id", infodetailid)
			    };
                return DataFactory.Run().ExecuteBySql(strSql, para);            
            }

            public bool DeleteInfoDetailBatch(string[] detailid)
            {         
                bool result;
                try
                {
                    StringBuilder[] sqls = new StringBuilder[detailid.Length];
                    object[] objs = new object[detailid.Length];
                
                    for (int i = 0; i < detailid.Length; i++)
                    {
                        string item = detailid[i];
                        if (item.Length > 0)
                        {
                            StringBuilder sbdel = new StringBuilder();
                            sbdel.Append("Update Visa_InfoDetail set deletemark=0 where InfoDetail_ID = @id");                       
                            SqlParam[] parm = new SqlParam[]
						    {
							    new SqlParam("@id", item)                           
						    };
                            sqls[i] = sbdel;
                            objs[i] = parm;
                        }
                    }
                    result = (DataFactory.Run().BatchExecuteBySql(sqls, objs) >= 0);
                }
                catch
                {
                    result = false;
                }
                return result;

            }

            public void UpdateInfoNo(string infoid)
            {
                Hashtable ht = new Hashtable();
                ht.Add("info_id", infoid);
                DataFactory.Run().ExecuteByProc("usp_SetInfoNo", ht);
            }
            #endregion
        #endregion

        
        //插入国家签证
        public void Info_InsertCertCity(string Countryid, string certtypeid, string userid, 
                string username,decimal ldc_price)
        {
            Hashtable ht = new Hashtable();
            ht.Add("Countryid", Countryid);
            ht.Add("certtype", certtypeid);
            ht.Add("userid", userid);
            ht.Add("username", username);
            ht.Add("price", ldc_price);
            DataFactory.Run().ExecuteByProc("usp_InsertCertCity", ht);
        }

        //插入签证类型
        public string Info_InsertCertType( string certname, string userid, string username)
        {
            SqlParam[] parm = new SqlParam[]
			{
				new SqlParam("@name", certname),
                new SqlParam("@userid", userid),
                new SqlParam("@username", username)
			};

            StringBuilder sb = new StringBuilder();
            sb.Append("exec usp_InsertCertType ");
            sb.Append(" @name=@name, @userid=@userid,@username=@username");
            
            string ls_rtn = Convert.ToString(DataFactory.Run().GetObjectValue(sb, parm));
            //删除缓存
            CacheHelper.RemoveAllCache("KeySysParm");
            return ls_rtn;            
        }

        //插入供应商信息
        public string Info_InsertSupply( string SupplyName, string userid, string username)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("exec usp_InsertSupply ");
            sb.Append(" @SupplyName=@SupplyName, @userid=@userid,@username=@username");

            SqlParam[] parm = new SqlParam[]
			{
				new SqlParam("@SupplyName", SupplyName),
                new SqlParam("@userid", userid),
                new SqlParam("@username", username)
			};

            return Convert.ToString(DataFactory.Run().GetObjectValue(sb, parm));                     
        }

        //插入委托方信息
        public string Info_InsertCustomer(string CustomerName, string userid, string username)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("exec usp_InsertTrust ");
            sb.Append(" @CustomerName=@CustomerName, @userid=@userid,@username=@username");

            SqlParam[] parm = new SqlParam[]
			{
				new SqlParam("@CustomerName", CustomerName),
                new SqlParam("@userid", userid),
                new SqlParam("@username", username)
			};            
            string ls_rtn = Convert.ToString(DataFactory.Run().GetObjectValue(sb, parm));
            //移除缓存
            CacheHelper.RemoveAllCache("KeyTrustInfo");
            return ls_rtn;
        }

        //插入供应商报价
        public void Info_SupplyPrice(string SupplyID, string Countryid, string certtype,
            decimal price,string userid, string username)
        {
            Hashtable ht = new Hashtable();
            ht.Add("SupplyID", SupplyID);
            ht.Add("Countryid", Countryid);
            ht.Add("certtype", certtype);
            ht.Add("price", price);
            ht.Add("userid", userid);
            ht.Add("username", username);
            DataFactory.Run().ExecuteByProc("usp_InsertSupplyCity", ht);  
        }

        #region 副总订单维护
        public string InfoDetail_insertspecial(string infoid, string ls_name, string ls_city, string ls_type,
                string ls_supply, decimal customerprice, decimal supplyprice, string ls_userid, string ls_username)
        {
            SqlParam[] parm = new SqlParam[]
			{
				new SqlParam("@infoid", infoid),
                new SqlParam("@cityid", ls_city),
                new SqlParam("@certtype", ls_type),
                new SqlParam("@supplyid", ls_supply),
                new SqlParam("@user", ls_name),
                new SqlParam("@Cprice", customerprice),
                new SqlParam("@Sprice", supplyprice),
                new SqlParam("@CreateUserid", ls_userid),
                new SqlParam("@CreateUserName", ls_username),
			};
            
            StringBuilder sb = new StringBuilder();
            sb.Append("exec usp_InsertInfoDetailSpecial ");
            sb.Append(" @infoid=@infoid,@cityid=@cityid,@certtype=@certtype,");
            sb.Append(" @supplyid=@supplyid,@user=@user,@Cprice=@Cprice,@Sprice=@Sprice,");
            sb.Append(" @CreateUserid=@CreateUserid,@CreateUserName=@CreateUserName");

            return Convert.ToString(DataFactory.Run().GetObjectValue(sb, parm));            
        }

        public void INFO_SetInfoNoSpe(string infoid)
        {
            Hashtable ht = new Hashtable();
            ht.Add("info_id", infoid);
            DataFactory.Run().ExecuteByProc("usp_SetInfoNoSpe", ht);
        }
        #endregion

        #region 订单审核

        //获取订单审核信息列表
        public DataTable GetInfoAuditPage(StringBuilder SqlWhere, IList<SqlParam> IList_param, int pageIndex, int pageSize, ref int count)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select detail.InfoDetail_ID,Convert(varchar(10),info.info_getdate,120) as info_date ,");
            strSql.Append("	detail.InfoDetail_user,city.City_name,certtype.parm_Value as certname, ");
            strSql.Append(" detail.InfoDetail_userphone,fulldata.parm_Value as fulldata,detail.createdate, ");
            strSql.Append("	detail.InfoDetail_remark,infoflag.parm_Value as infoflag, ");
            strSql.Append("	case u.isViewSupply when 1 then supply.Supply_Name else N'***' end as Supply_Name, ");
            strSql.Append(" case u.isViewPrice when 1 then cast(detail.InfoDetai_BegPrice as varchar(10)) else N'***' end as price, ");
            strSql.Append("	case u.isViewCost when 1 then cast(detail.InfoDetai_EndPrice as varchar(10)) else N'***' end as cost, ");
            strSql.Append("	detail.infoDetail_remark as rmk2,dbo.utf_GetRemrk(detail.infoDetail_remark) as rmk ");
            strSql.Append("from Visa_Info info ");
            strSql.Append("	inner join Visa_InfoDetail detail on info.Info_ID=detail.Info_ID ");
            strSql.Append("	inner join Base_City city on city.City_id=detail.City_id ");
            strSql.Append("	inner join utf_GetSysParm('certtype') as certtype on certtype.parm_ID=detail.Ref_CertType ");
            strSql.Append("	left join Base_supply supply on supply.Supply_id=detail.Supply_id ");
            strSql.Append("	inner join utf_GetSysParm('infoflag') as infoflag on infoflag.parm_Key=detail.Ref_infoflag ");
            strSql.Append("	inner join utf_GetSysParm('Data_integrity') as fulldata on fulldata.parm_Key=detail.Data_integrity ");
            strSql.Append("	left join Sys_UserInfo u on u.User_ID=@userid ");
            strSql.Append("where info.DeleteMark=1 and detail.DeleteMark=1 and detail.Ref_infoflag ='raise' and ");
            strSql.Append(" (detail.InfoDetail_operationid =@userid or @userid='48f3889c-af8d-401f-ada2-c383031af92d') "); 
            strSql.Append(SqlWhere);
            return DataFactory.Run().GetPageList(strSql.ToString(), IList_param.ToArray<SqlParam>(), "createdate", "asc", pageIndex, pageSize, ref count);
        }

        //获取审核信息列表---导出
        public DataTable GetInfoAuditList(StringBuilder SqlWhere, IList<SqlParam> IList_param)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ROW_NUMBER() over( order by detail.InfoDetail_user) as '序号',");
            strSql.Append(" Convert(varchar(10),info.info_getdate,120) as '收签日期' ,");
            strSql.Append("	detail.InfoDetail_user as '客人姓名',city.City_name as '签证国家',");
            strSql.Append("	certtype.parm_Value as '签证类型', ");
            strSql.Append("	detail.InfoDetail_userphone as '联系电话', fulldata.parm_Value as '资料', ");
            strSql.Append("	detail.InfoDetail_remark as '签证备注',infoflag.parm_Value as '订单状态', ");
            strSql.Append(" case u.isViewSupply when 1 then supply.Supply_Name else N'***' end as '供应商',");
            strSql.Append(" case u.isViewPrice when 1 then cast(detail.InfoDetai_BegPrice as varchar(10)) else N'***' end as '应收款', ");
            strSql.Append(" case u.isViewCost when 1 then cast(detail.InfoDetai_EndPrice else N'***' as varchar(10)) end as '应付款', ");
            strSql.Append("  detail.infoDetail_remark as '备注' ");
            strSql.Append("from Visa_Info info ");
            strSql.Append("	inner join Visa_InfoDetail detail on info.Info_ID=detail.Info_ID ");
            strSql.Append("	inner join Base_City city on city.City_id=detail.City_id ");
            strSql.Append("	inner join utf_GetSysParm('certtype') as certtype on certtype.parm_ID=detail.Ref_CertType ");
            strSql.Append("	left join Base_supply supply on supply.Supply_id=detail.Supply_id ");
            strSql.Append("	inner join utf_GetSysParm('infoflag') as infoflag on infoflag.parm_Key=detail.Ref_infoflag ");
            strSql.Append("	inner join utf_GetSysParm('Data_integrity') as fulldata on fulldata.parm_Key=detail.Data_integrity ");
            strSql.Append("	left join Sys_UserInfo u on u.User_ID=@userid ");
            strSql.Append("where info.DeleteMark=1 and detail.DeleteMark=1 and detail.Ref_infoflag ='raise' and "); 
            strSql.Append(" (detail.InfoDetail_operationid =@userid or @userid='48f3889c-af8d-401f-ada2-c383031af92d') ");
            strSql.Append(SqlWhere);
            return DataFactory.Run().GetDataTableBySQL(strSql, IList_param.ToArray<SqlParam>());            
        }

        //单笔信息审核
        public Hashtable GetInfoAuditForm(string detailid)
        {
            StringBuilder strSql = new StringBuilder();  
            strSql.Append("select trust.trust_name,Convert(varchar(10),info.info_getdate,120) as info_date, ");
            strSql.Append("	info.info_operation,detail.InfoDetail_user,detail.InfoDetail_cardno,detail.InfoDetail_userphone, ");
            strSql.Append("	detail.City_id,detail.Ref_CertType,detail.InfoDetai_BegPrice, ");
            strSql.Append("	detail.Supply_id,detail.InfoDetai_EndPrice,detail.Data_integrity, ");
            strSql.Append("	detail.InfoDetail_operationid,detail.InfoDetail_remark,detail.user_audit, ");
            strSql.Append("	detail.Ref_moneyflag,detail.Ref_moneytype,detail.Financel_remark,detail.ref_infoflag ");
            strSql.Append("from Visa_Info info ");
            strSql.Append(" inner join Visa_InfoDetail detail on info.Info_ID=detail.Info_ID ");
            strSql.Append(" inner join Base_Trust trust on trust.trust_id=info.trust_id ");
            strSql.Append(" where info.DeleteMark=1 and detail.DeleteMark=1 ");
            strSql.Append("  and detail.InfoDetail_ID=@detailid ");
            
            SqlParam[] parm = new SqlParam[]
			{
				new SqlParam("@detailid", detailid)
			};
            return DataTableHelper.DataTableToHashtable(DataFactory.Run().GetDataTableBySQL(strSql, parm));
        }

        //批量修改
        public bool InfoAudit_BatchUpdate(string[] pkVal, string ls_city, string ls_certtype,string ls_supplyid,
           string ls_operationid, string ls_operationname, string ls_moneytype,string ls_moneyflag,string ls_infoflag,
            string ls_price,string ls_cost,string ls_fulldata,string ls_rmk,string ls_Financel_remark,
            string ls_updateuserid, string ls_updateusername)
        {      
            StringBuilder[] sqls = new StringBuilder[pkVal.Length];
            object[] objs = new object[pkVal.Length];
            for (int i = 0; i < pkVal.Length; i++)
            {
                 string detailid = pkVal[i];
                 if (detailid.Length > 0)
                 {
                     StringBuilder sbadd = new StringBuilder();
                     sbadd.Append(" exec usp_update_InfoAuditBatchUpdate @detailid=@detailid,");
                     sbadd.Append(" @fulldate=@fulldate,@infoflag=@infoflag,@cityid=@cityid,");
                     sbadd.Append(" @Ref_CertType=@Ref_CertType,@Supply_id=@Supply_id,");
                     sbadd.Append(" @InfoDetail_operationid=@InfoDetail_operationid,");
                     sbadd.Append(" @InfoDetail_operationname=@InfoDetail_operationname,");
                     sbadd.Append(" @Ref_moneytype=@Ref_moneytype,@Ref_moneyflag=@Ref_moneyflag,");
                     sbadd.Append(" @InfoDetai_BegPrice=@InfoDetai_BegPrice,@InfoDetai_EndPrice=@InfoDetai_EndPrice,");
                     sbadd.Append(" @InfoDetail_remark=@InfoDetail_remark,@Financel_remark=@Financel_remark,");
                     sbadd.Append(" @ModifyUserId=@ModifyUserId,@ModifyUserName=@ModifyUserName");
                                         
                     SqlParam[] parm = new SqlParam[]
                        {
                            new SqlParam("@detailid", detailid),
                            new SqlParam("@fulldate", ls_fulldata),
                            new SqlParam("@infoflag", ls_infoflag),
                            new SqlParam("@cityid", ls_city),
                            new SqlParam("@Ref_CertType", ls_certtype),
                            new SqlParam("@Supply_id", ls_supplyid),
                            new SqlParam("@InfoDetail_operationid", ls_operationid),
                            new SqlParam("@InfoDetail_operationname", ls_operationname),
                            new SqlParam("@Ref_moneytype", ls_moneytype),
                            new SqlParam("@Ref_moneyflag", ls_moneyflag),
                            new SqlParam("@InfoDetai_BegPrice", ls_price),
                            new SqlParam("@InfoDetai_EndPrice", ls_cost),
                            new SqlParam("@InfoDetail_remark", ls_rmk),
                            new SqlParam("@Financel_remark", ls_Financel_remark),
                            new SqlParam("@ModifyUserId", ls_updateuserid),
                            new SqlParam("@ModifyUserName", ls_updateusername)
                        };

                     sqls[i] = sbadd;
                     objs[i] = parm;                     
                 }
             }
            return (DataFactory.Run().BatchExecuteBySql(sqls, objs) >= 0);
        }
        #endregion

        #region 送签页面
        //获取寄件信息列表
        public DataTable GetInfoShipPage(StringBuilder SqlWhere, IList<SqlParam> IList_param, int pageIndex, int pageSize, ref int count)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select detail.InfoDetail_ID,detail.InfoDetail_user,city.City_name,certtype.parm_Value as certname, ");
            strSql.Append("	supply.Supply_Name,Convert(varchar(10),info.info_getdate,120) as info_date,trust.trust_name,infoflag.parm_Value as flagname, ");
            strSql.Append(" detail.InfoDetail_operationname as user_oper,detail.user_audit, ");            
            strSql.Append(" detail.InfoDetail_remark as rmk2,dbo.utf_GetRemrk(detail.InfoDetail_remark) as rmk,detail.createdate ");
            strSql.Append("from Visa_Info info ");
            strSql.Append(" inner join Visa_InfoDetail detail on info.Info_ID=detail.Info_ID ");
            strSql.Append(" inner join Base_City city on city.City_id=detail.City_id ");
            strSql.Append(" inner join utf_GetSysParm('certtype') certtype on certtype.parm_ID=detail.Ref_CertType ");
            strSql.Append(" inner join utf_GetSysParm('infoflag') infoflag on infoflag.parm_Key=detail.Ref_infoflag  ");
            strSql.Append(" inner join Base_supply supply on supply.Supply_id=detail.Supply_id ");
            strSql.Append(" inner join Base_Trust trust on trust.trust_id=info.trust_id  ");
            strSql.Append(" where info.DeleteMark=1 and detail.DeleteMark=1 and ");
            strSql.Append("  detail.Data_integrity=1 and detail.Ref_infoflag ='audit' ");
            strSql.Append(SqlWhere);
            return DataFactory.Run().GetPageList(strSql.ToString(), IList_param.ToArray<SqlParam>(), "createdate", "asc", pageIndex, pageSize, ref count);
        }

        // 数据导出
        public DataTable GetInfoShipExport(StringBuilder SqlWhere, IList<SqlParam> IList_param)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ROW_NUMBER() over(order by detail.InfoDetail_user asc) as '序号', ");
            strSql.Append("	detail.InfoDetail_user as '客户',city.City_name as '签证国家', ");
            strSql.Append("	infoflag.parm_Value as '订单状态', ");
            strSql.Append(" detail.InfoDetail_operationname as '操作员',detail.user_audit as '审核员', ");
            strSql.Append("	certtype.parm_Value as '签证类型',detail.InfoDetail_remark as '签证备注' ");
            strSql.Append("from Visa_Info info ");
            strSql.Append(" inner join Visa_InfoDetail detail on info.Info_ID=detail.Info_ID ");
            strSql.Append(" inner join Base_City city on city.City_id=detail.City_id ");
            strSql.Append(" inner join utf_GetSysParm('certtype') certtype on certtype.parm_ID=detail.Ref_CertType ");
            strSql.Append(" inner join utf_GetSysParm('infoflag') infoflag on infoflag.parm_Key=detail.Ref_infoflag ");
            strSql.Append(" inner join Base_supply supply on supply.Supply_id=detail.Supply_id ");
            strSql.Append(" where info.DeleteMark=1 and detail.DeleteMark=1 and ");
            strSql.Append("  detail.Data_integrity=1 and detail.Ref_infoflag ='audit' )");
            strSql.Append(SqlWhere);

            return DataFactory.Run().GetDataTableBySQL(strSql, IList_param.ToArray<SqlParam>());
        }

        //批量送签
        public int InfoShip_BatchUpdate(string pkVal, string ls_date,string updateid,string updatename,string ls_userSendto)
        {
            Hashtable ht = new Hashtable();
            ht.Add("key", pkVal);
            ht.Add("senddate", ls_date);
            ht.Add("updateid",  updateid);
            ht.Add("updatename", updatename);
            ht.Add("usersandto", ls_userSendto);           
            return DataFactory.Run().ExecuteByProc("usp_UpdateInfoShip", ht) ;            
        }

        //批量修改国家，类型，供应商，姓名，签证备注
        public bool InfoShip_BatchChange(string[] pkVal, string ls_city,string ls_certtype,string ls_supply,
            string ls_cusname, string ls_rmk, string ls_updateuserid, string ls_updateusername)
        {
            StringBuilder[] sqls = new StringBuilder[pkVal.Length];
            object[] objs = new object[pkVal.Length];
            for (int i = 0; i < pkVal.Length; i++)
            {
                string detailid = pkVal[i];
                if (detailid.Length > 0)
                {
                    StringBuilder sbadd = new StringBuilder();
                    sbadd.Append(" exec usp_update_InfoShip_BatchChange @detailid=@detailid,");
                    sbadd.Append(" @cityid=@cityid,@Ref_CertType=@Ref_CertType,@Supply_id=@Supply_id,");  
                    sbadd.Append(" @cusname=@cusname,@InfoDetail_remark=@InfoDetail_remark,");                    
                    sbadd.Append(" @ModifyUserId=@ModifyUserId,@ModifyUserName=@ModifyUserName");
                    
                    SqlParam[] parm = new SqlParam[]
                        {
                            new SqlParam("@detailid", detailid),                            
                            new SqlParam("@cityid", ls_city),
                            new SqlParam("@Ref_CertType", ls_certtype),
                            new SqlParam("@Supply_id", ls_supply),                           
                            new SqlParam("@cusname", ls_cusname),
                            new SqlParam("@InfoDetail_remark", ls_rmk),
                            new SqlParam("@ModifyUserId", ls_updateuserid),
                            new SqlParam("@ModifyUserName", ls_updateusername)
                        };

                    sqls[i] = sbadd;
                    objs[i] = parm;
                }
            }
            return (DataFactory.Run().BatchExecuteBySql(sqls, objs) >= 0);           
        }

        #endregion

        #region 未寄资料查询
        //未寄资料查询
        public DataTable Report_InfoUnShipList(StringBuilder SqlWhere, IList<SqlParam> IList_param, int pageIndex, int pageSize, ref int count)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select detail.InfoDetail_ID,trust.trust_name,info.info_operation ,detail.InfoDetail_user ,city.City_name, ");
            strSql.Append("	certtype.parm_Value as certtype,Convert(varchar(10),info.info_getdate,120) as info_getdate,	");
	        strSql.Append(" detail.InfoDetail_cardno ,detail.InfoDetail_userphone,infoflag.parm_Value as infoflag, ");
            strSql.Append(" detail.InfoDetail_remark as rmk2,dbo.utf_GetRemrk(detail.InfoDetail_remark) as rmk ");
	        strSql.Append("	from Visa_Info info ");
            strSql.Append("	inner join Visa_InfoDetail detail on info.Info_ID=detail.Info_ID ");
            strSql.Append("	inner join Base_City city on city.City_id=detail.City_id ");
	        strSql.Append("	left join Base_supply supply on supply.Supply_id=detail.Supply_id ");
            strSql.Append("	inner join base_trust trust on trust.trust_id=info.trust_id ");
            strSql.Append("	inner join utf_GetSysParm('certtype') as certtype on certtype.parm_ID=detail.Ref_CertType ");
            strSql.Append("	inner join utf_GetSysParm('infoflag') as infoflag on infoflag.parm_Key=detail.Ref_infoflag ");
            strSql.Append("	where info.DeleteMark=1 and detail.DeleteMark=1 and ");
            strSql.Append("	 ((detail.Data_integrity=0 and detail.Ref_infoflag='audit') or detail.Ref_infoflag='raise')");
            strSql.Append(SqlWhere);

            return DataFactory.Run().GetPageList(strSql.ToString(), IList_param.ToArray<SqlParam>(), "trust_name", "asc", pageIndex, pageSize, ref count);
        }
        #endregion

        #region 小石
        //获取未送签资料
        public DataTable shi_GetVisaInfoPage(StringBuilder SqlWhere, IList<SqlParam> IList_param, int pageIndex, int pageSize, ref int count)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select Visa_Info.info_getdate,Base_trust.trust_name,visa_infodetail.InfoDetail_user,Visa_Infodetail.[InfoDetail_cardno],Visa_Infodetail.[InfoDetail_userphone],city.City_name,CertType.parm_Value,convert(varchar,Visa_InfoDetail.Data_integrity)+'a' as Data_integrity,Visa_Infodetail.[InfoDetail_remark],visa_post.Submit_Remark,Visa_Infodetail.[Ref_infoflag],Visa_Infodetail.[InfoDetail_operationname],Visa_Infodetail.InfoDetail_ID,Visa_Infodetail.Data_CompletionDate,Visa_Infodetail.Financel_remark from Visa_Info");
            strSql.Append(" inner join Visa_InfoDetail on Visa_Info.Info_ID = Visa_InfoDetail.Info_ID ");
            strSql.Append(" left join Base_supply on Base_supply.Supply_id = Visa_InfoDetail.Supply_id ");
            strSql.Append(" left join Base_SupplyCity on Base_SupplyCity.City_id=Visa_InfoDetail.City_id and Base_SupplyCity.Ref_CertType=Visa_InfoDetail.Ref_CertType and Base_SupplyCity.Supply_id=Visa_InfoDetail.Supply_id and Base_SupplyCity.DeleteMark=1");
            strSql.Append(" inner JOIN Base_trust on Base_trust.trust_id = Visa_Info.trust_id");
            strSql.Append(" inner join Base_City city on city.City_id=Visa_InfoDetail.City_id");
            strSql.Append(" inner join utf_GetSysParm('certtype') CertType on CertType.parm_ID=Visa_InfoDetail.Ref_CertType");
            strSql.Append(" inner join utf_GetSysParm('infoflag') infoflag on infoflag.parm_Key=Visa_Info.Ref_infoflag");
            strSql.Append(" left outer join visa_post on visa_post.InfoDetail_ID = Visa_InfoDetail.InfoDetail_ID ");
            //strSql.Append(" where Visa_InfoDetail. Data_integrity=0 and Visa_Info.DeleteMark=1 and Visa_InfoDetail.DeleteMark=1");
            strSql.Append(" where Visa_Infodetail.Ref_infoflag='audit' and Visa_Info.DeleteMark=1 and Visa_InfoDetail.DeleteMark=1 ");
            strSql.Append(SqlWhere);
            return DataFactory.Run().GetPageList(strSql.ToString(), IList_param.ToArray<SqlParam>(), "City_name", "asc", pageIndex, pageSize, ref count);
        }

        //获取已签证资料
        public DataTable shi_GetVisaInfo2Page(StringBuilder SqlWhere, IList<SqlParam> IList_param, int pageIndex, int pageSize, ref int count)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select Base_supply.Supply_Name,Base_trust.trust_name,visa_infodetail.InfoDetail_user,Visa_Infodetail.[InfoDetail_cardno],city.City_name,CertType.parm_Value,visa_post.Submit_Remark,Visa_Infodetail.[Ref_infoflag],Visa_Infodetail.[InfoDetail_operationname],Visa_Infodetail.InfoDetail_ID,convert(varchar(10),Visa_Infodetail.Data_CompletionDate,120) as Data_CompletionDate from Visa_Info");
            strSql.Append(" inner join Visa_InfoDetail on Visa_Info.Info_ID = Visa_InfoDetail.Info_ID ");
            strSql.Append(" left join Base_supply on Base_supply.Supply_id = Visa_InfoDetail.Supply_id ");
            strSql.Append(" left join Base_SupplyCity on Base_SupplyCity.City_id=Visa_InfoDetail.City_id and Base_SupplyCity.Ref_CertType=Visa_InfoDetail.Ref_CertType and Base_SupplyCity.Supply_id=Visa_InfoDetail.Supply_id and Base_SupplyCity.DeleteMark=1");
            strSql.Append(" inner JOIN Base_trust on Base_trust.trust_id = Visa_Info.trust_id");
            strSql.Append(" inner join Base_City city on city.City_id=Visa_InfoDetail.City_id");
            strSql.Append(" inner join utf_GetSysParm('certtype') CertType on CertType.parm_ID=Visa_InfoDetail.Ref_CertType");
            strSql.Append(" inner join utf_GetSysParm('infoflag') infoflag on infoflag.parm_Key=Visa_Info.Ref_infoflag");
            strSql.Append(" left outer join visa_post on visa_post.InfoDetail_ID = Visa_InfoDetail.InfoDetail_ID ");
            strSql.Append(" where Visa_Infodetail.[Ref_infoflag]='SendTO' and Visa_Info.DeleteMark=1 and Visa_InfoDetail.DeleteMark=1");
            strSql.Append(SqlWhere);
            return DataFactory.Run().GetPageList(strSql.ToString(), IList_param.ToArray<SqlParam>(), "City_name", "asc", pageIndex, pageSize, ref count);
        }


        //出签查询
        public DataTable shi_GetVisaInfo3Page(StringBuilder SqlWhere, IList<SqlParam> IList_param, int pageIndex, int pageSize, ref int count)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select Visa_Infodetail.[InfoDetail_Submitdate],visa_infodetail.InfoDetail_user,Visa_Infodetail.[InfoDetail_cardno],Base_trust.trust_name,Base_supply.Supply_Name,city.City_name,CertType.parm_Value,Visa_Infodetail.[Ref_infoflag],Visa_Infodetail.[InfoDetail_userphone],Visa_Infodetail.[InfoDetai_EndPrice],Visa_Infodetail.[InfoDetail_Receivedate],Visa_Infodetail.[Infodetail_rejectreason],visa_post.Design_Remark,Visa_Infodetail.InfoDetail_ID from  Visa_Info");
            strSql.Append(" inner join Visa_InfoDetail on Visa_Info.Info_ID = Visa_InfoDetail.Info_ID");
            strSql.Append(" left  join Base_supply     on  Base_supply.Supply_id = Visa_InfoDetail.Supply_id");
            strSql.Append(" left  join Base_SupplyCity on  Base_SupplyCity.City_id=Visa_InfoDetail.City_id and Base_SupplyCity.Ref_CertType=Visa_InfoDetail.Ref_CertType and Base_SupplyCity.Supply_id=Visa_InfoDetail.Supply_id and Base_SupplyCity.DeleteMark=1");
            strSql.Append(" inner JOIN Base_trust      on  Base_trust.trust_id = Visa_Info.trust_id");
            strSql.Append(" inner join Base_City city on city.City_id=Visa_InfoDetail.City_id");
            strSql.Append(" inner join utf_GetSysParm('certtype') CertType on CertType.parm_ID=Visa_InfoDetail.Ref_CertType");
            strSql.Append(" inner join utf_GetSysParm('infoflag') infoflag on infoflag.parm_Key=Visa_Info.Ref_infoflag");
            strSql.Append(" left outer join visa_post   on visa_post.InfoDetail_ID = Visa_InfoDetail.InfoDetail_ID");
            strSql.Append(" where (Visa_Infodetail.[Ref_infoflag]=N'ResultOK' or Visa_Infodetail.[Ref_infoflag]=N'ResultFalse') and Visa_Info.DeleteMark=1 and Visa_InfoDetail.DeleteMark=1");
            strSql.Append(SqlWhere);
            return DataFactory.Run().GetPageList(strSql.ToString(), IList_param.ToArray<SqlParam>(), "InfoDetail_Submitdate", "asc", pageIndex, pageSize, ref count);
        }

        //用户寄件查询
        public DataTable shi_GetVisaInfo4Page(StringBuilder SqlWhere, IList<SqlParam> IList_param, int pageIndex, int pageSize, ref int count)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select visa_infodetail.InfoDetail_user,Visa_Infodetail.[InfoDetail_Submitdate],Visa_Infodetail.[InfoDetail_Receivedate],Visa_Infodetail.[InfoDetail_userphone],city.City_name,CertType.parm_Value,Visa_Infodetail.[InfoDetai_EndPrice],Visa_Infodetail.ref_moneyflag,VISA_POST.INVOICE,VISA_POST.ref_POSTBY,VISA_POST.POSTNO,VISA_POST.POST_REMARK,Base_trust.trust_name,Visa_Infodetail.InfoDetail_ID from Visa_Info");
            strSql.Append(" inner join Visa_InfoDetail on  Visa_Info.Info_ID = Visa_InfoDetail.Info_ID ");
            strSql.Append(" left  join Base_supply on  Base_supply.Supply_id = Visa_InfoDetail.Supply_id");
            strSql.Append(" left  join Base_SupplyCity on  Base_SupplyCity.City_id=Visa_InfoDetail.City_id and Base_SupplyCity.Ref_CertType=Visa_InfoDetail.Ref_CertType and Base_SupplyCity.Supply_id=Visa_InfoDetail.Supply_id and Base_SupplyCity.DeleteMark=1 ");
            strSql.Append(" inner JOIN Base_trust      on  Base_trust.trust_id = Visa_Info.trust_id  ");
            strSql.Append(" inner join Base_City city on city.City_id=Visa_InfoDetail.City_id");
            strSql.Append(" inner join utf_GetSysParm('certtype') CertType on CertType.parm_ID=Visa_InfoDetail.Ref_CertType");
            strSql.Append(" inner join utf_GetSysParm('infoflag') infoflag on infoflag.parm_Key=Visa_Info.Ref_infoflag");
            strSql.Append(" left outer join visa_post   on visa_post.InfoDetail_ID = Visa_InfoDetail.InfoDetail_ID ");
            strSql.Append(" where Visa_Info.DeleteMark=1 and Visa_InfoDetail.DeleteMark=1 and Visa_Infodetail.InfoDetail_Status='POST'");
            strSql.Append(SqlWhere);
            return DataFactory.Run().GetPageList(strSql.ToString(), IList_param.ToArray<SqlParam>(), "InfoDetail_Receivedate", "asc", pageIndex, pageSize, ref count);
        }

        //销签查询
        public DataTable shi_GetVisaInfo5Page(StringBuilder SqlWhere, IList<SqlParam> IList_param, int pageIndex, int pageSize, ref int count)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select Base_trust.trust_name,visa_infodetail.InfoDetail_user,Visa_Infodetail.[InfoDetail_cardno],Visa_Infodetail.[InfoDetail_userphone],Visa_Infodetail.[InfoDetail_Submitdate],Visa_Infodetail.[InfoDetail_Receivedate],city.City_name,CertType.parm_Value,visa_infodetail.infodetail_design,VISA_POST.Design_Remark,Visa_Infodetail.InfoDetail_ID from Visa_Info");
            strSql.Append(" inner join Visa_InfoDetail on Visa_Info.Info_ID = Visa_InfoDetail.Info_ID ");
            strSql.Append(" left join Base_supply on Base_supply.Supply_id = Visa_InfoDetail.Supply_id");
            strSql.Append(" left join Base_SupplyCity on Base_SupplyCity.City_id=Visa_InfoDetail.City_id and Base_SupplyCity.Ref_CertType=Visa_InfoDetail.Ref_CertType and Base_SupplyCity.Supply_id=Visa_InfoDetail.Supply_id and Base_SupplyCity.DeleteMark=1");
            strSql.Append(" inner JOIN Base_trust on Base_trust.trust_id = Visa_Info.trust_id");
            strSql.Append(" inner join Base_City city on city.City_id=Visa_InfoDetail.City_id");
            strSql.Append(" inner join utf_GetSysParm('certtype') CertType on CertType.parm_ID=Visa_InfoDetail.Ref_CertType");
            strSql.Append(" inner join utf_GetSysParm('infoflag') infoflag on infoflag.parm_Key=Visa_Info.Ref_infoflag");
            strSql.Append(" left outer join visa_post on visa_post.InfoDetail_ID = Visa_InfoDetail.InfoDetail_ID ");
            strSql.Append(" where Visa_Info.DeleteMark=1 and Visa_InfoDetail.DeleteMark=1 and Visa_Infodetail.InfoDetail_Status='POST'");
            strSql.Append(SqlWhere);
            return DataFactory.Run().GetPageList(strSql.ToString(), IList_param.ToArray<SqlParam>(), "trust_name", "asc", pageIndex, pageSize, ref count);
        }
        //订单行管理--2015.01.06 modify by xiaoshi
        public DataTable GetVisaInfoDetailPage(StringBuilder SqlWhere, IList<SqlParam> IList_param, int pageIndex, int pageSize, ref int count)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  Visa_Info.[info_getdate],base_trust.trust_name,[InfoDetail_user],[InfoDetail_userphone],Visa_Info.[Ref_infoflag],[InfoDetail_cardno],convert(varchar,[Data_integrity])+'a' as Data_integrity,[InfoDetail_remark],Visa_Info.[Info_ID],Visa_Info.[Ref_moneyflag],[InfoDetail_Submitdate],[InfoDetail_Receivedate],[InfoDetail_operationname],[Data_CompletionDate] from  Visa_Info  inner join Visa_InfoDetail on Visa_Info.Info_ID = Visa_InfoDetail.Info_ID left outer join Base_supply on Base_supply.Supply_id = Visa_InfoDetail.Supply_id left outer join Base_CertCity on Base_CertCity.Supply_ID = Base_supply.Supply_id  ");
            strSql.Append(" inner JOIN Base_trust      on  Base_trust.trust_id = Visa_Info.trust_id");
            strSql.Append(" where Visa_Info.DeleteMark=1 and Visa_InfoDetail.DeleteMark=1 ");

            strSql.Append(SqlWhere);
            return DataFactory.Run().GetPageList(strSql.ToString(), IList_param.ToArray<SqlParam>(), "Info_ID", "asc", pageIndex, pageSize, ref count);
        }
        public DataTable shi_GetVisaInfo6Page(StringBuilder SqlWhere, IList<SqlParam> IList_param, int pageIndex, int pageSize, ref int count)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select visa_infodetail.InfoDetail_user,Visa_Infodetail.[InfoDetail_cardno],Base_City.City_name,CertType.parm_Value,visa_post.Design_Remark,Visa_Infodetail.Data_CompletionDate from Visa_Info   ");
            strSql.Append(" inner join Visa_InfoDetail      on  Visa_Info.Info_ID = Visa_InfoDetail.Info_ID ");
            strSql.Append(" left  join Base_supply     on  Base_supply.Supply_id = Visa_InfoDetail.Supply_id");
            strSql.Append(" left  join Base_City on  Base_City.City_id=Visa_InfoDetail.City_id");
            strSql.Append(" inner join utf_GetSysParm('certtype') CertType on CertType.parm_ID=Visa_InfoDetail.Ref_CertType");
            strSql.Append(" left outer join visa_post on visa_post.InfoDetail_ID = Visa_InfoDetail.InfoDetail_ID");
            strSql.Append(" where Visa_Info.DeleteMark=1 and Visa_InfoDetail.DeleteMark=1 and Visa_Infodetail.InfoDetail_Status='sendto'");
            strSql.Append(SqlWhere);
            return DataFactory.Run().GetPageList(strSql.ToString(), IList_param.ToArray<SqlParam>(), "Data_CompletionDate", "asc", pageIndex, pageSize, ref count);
        }
        #endregion

        #region 工作日历
        public DataTable GetCalendarList(string begdate, string enddate) {
            Hashtable ht = new Hashtable();
            ht.Add("beg", begdate);
            ht.Add("end", enddate);
            return DataFactory.Run().GetDataTableProc("usp_GetCalendar", ht);
        }
        #endregion

    }
}